import { execute, select } from '../../utils/db';
import type { TrainerFile } from '../../types/trainerFile';
import { Page, PageReq } from '../../types/page';

export class TrainerFileDao {
  // 新增患者报告文件
  static async create(trainerFile: Omit<TrainerFile, 'id'>) : Promise<number | undefined> {
    const sql = `INSERT INTO trainer_file (
      trainer_id, file_name, file_path
    ) VALUES (?, ?, ?)`;
    
    const params = [
      trainerFile.trainer_id, 
      trainerFile.file_name, 
      trainerFile.file_path
    ];

    return (await execute(sql, params)).lastInsertId;
  }

  // 更新患者报告文件信息
  static async update(trainerFile: Partial<TrainerFile>) {
    const fields = [];
    const params = [];
    
    for (const [key, value] of Object.entries(trainerFile)) {
      if (key !== 'id' && value !== undefined) {
        fields.push(`${key} = ?`);
        params.push(value);
      }
    }
    
    if (fields.length === 0) {
      return Promise.resolve();
    }
    
    params.push(trainerFile.id);
    const sql = `UPDATE trainer_file SET ${fields.join(', ')} WHERE id = ?`;
    return execute(sql, params);
  }

  // 删除患者报告文件
  static async delete(id: number) {
    const sql = 'DELETE FROM trainer_file WHERE id = ?';
    return execute(sql, [id]);
  }

    // 删除患者报告文件
  static async deleteByTrainerId(trainer_id : number) {
    const sql = 'DELETE FROM trainer_file WHERE trainer_id = ?';
    return execute(sql, [trainer_id]);
  }

  // 根据ID查询患者报告文件
  static async findById(id: number): Promise<TrainerFile | null> {
    const sql = 'SELECT * FROM trainer_file WHERE id = ?';
    const result = await select<TrainerFile[]>(sql, [id]);
    return result.length > 0 ? result[0] : null;
  }

  // 根据患者ID查询所有报告文件
  static async findByTrainerId(trainer_id : number): Promise<TrainerFile[]> {
    const sql = 'SELECT * FROM trainer_file WHERE trainer_id = ? ORDER BY id DESC';
    return await select<TrainerFile[]>(sql, [trainer_id ]);
  }

  // 查询所有患者报告文件
  static async list(): Promise<TrainerFile[]> {
    const query = 'SELECT * FROM trainer_file ORDER BY id DESC';
    return await select<TrainerFile[]>(query);
  }

  // 执行原始SQL
  static async execute(sql: string, params?: any[]) {
    return execute(sql, params);
  }

  /**
   * 分页查询患者报告文件列表
   */
  static async page(params: PageReq<TrainerFile>): Promise<Page<TrainerFile>> {
    const { pageNum, pageSize } = params;
    const offset = (pageNum - 1) * pageSize;
    const fields = [];
    const values = [];
    const trainerFile = params.params;
    
    if (trainerFile.trainer_id) {
      fields.push('trainer_id = ?');
      values.push(trainerFile.trainer_id);
    }
    if (trainerFile.file_name) {
      fields.push('file_name like ?');
      values.push(`%${trainerFile.file_name}%`);
    }

    const condition = fields.length > 0 ? `WHERE ${fields.join(' AND ')}` : '';
    const countSql = `SELECT COUNT(*) as total FROM trainer_file ${condition}`;
    const listSql = `SELECT * FROM trainer_file ${condition} ORDER BY id DESC LIMIT ${pageSize} OFFSET ${offset}`;
    
    const totalResult = await select<{total: number}>(countSql, values);
    const listResult = await select<TrainerFile[]>(listSql, values);
    
    return {
      total: totalResult[0].total || 0,
      list: listResult || []
    };
  }
}